<!doctype html><html><head><meta charset=utf-8><meta name=viewport content="width=device-width,initial-scale=1"><meta name=robots content="noodp"><meta http-equiv=x-ua-compatible content="IE=edge, chrome=1"><title>PostgreSQL - 字符串函数汇总 - Yulin Lewis' Blog</title><meta name=keywords content="雨临Lewis,Java,hugo,hexo,博客"><meta name=Description content="不想当写手的码农不是好咸鱼_(xз」∠)_"><meta property="og:title" content="PostgreSQL - 字符串函数汇总"><meta property="og:description" content="前言
本文基于PostgreSQL 12.6版本，不同版本的函数可能存在差异。
拼接字符串
||是字符串连接操作符，在拼接字符串时要求前两个操作数至少有一个是字符串类型，不然会报错。如下：


1
2
3


select 'a' || 1;
select 2 || 'a' || 1;
select 2 || 44 || 'a' || 1; --Error"><meta property="og:type" content="article"><meta property="og:url" content="https://lewky.cn/posts/postgresql-string.html/"><meta property="og:image" content="https://lewky.cn/logo.png"><meta property="article:published_time" content="2021-02-18T22:44:03+08:00"><meta property="article:modified_time" content="2022-03-02T22:44:03+08:00"><meta name=twitter:card content="summary_large_image"><meta name=twitter:image content="https://lewky.cn/logo.png"><meta name=twitter:title content="PostgreSQL - 字符串函数汇总"><meta name=twitter:description content="前言
本文基于PostgreSQL 12.6版本，不同版本的函数可能存在差异。
拼接字符串
||是字符串连接操作符，在拼接字符串时要求前两个操作数至少有一个是字符串类型，不然会报错。如下：


1
2
3


select 'a' || 1;
select 2 || 'a' || 1;
select 2 || 44 || 'a' || 1; --Error"><meta name=application-name content="雨临Lewis的博客"><meta name=apple-mobile-web-app-title content="雨临Lewis的博客"><meta name=theme-color content="#ffffff"><meta name=msapplication-TileColor content="#da532c"><link rel="shortcut icon" type=image/x-icon href=/favicon.ico><link rel=icon type=image/png sizes=32x32 href=/favicon-32x32.png><link rel=icon type=image/png sizes=16x16 href=/favicon-16x16.png><link rel=apple-touch-icon sizes=180x180 href=/apple-touch-icon.png><link rel=mask-icon href=/safari-pinned-tab.svg color=#5bbad5><link rel=manifest href=/site.webmanifest><link rel=canonical href=https://lewky.cn/posts/postgresql-string.html/><link rel=prev href=https://lewky.cn/posts/remote-desktop-issue.html/><link rel=next href=https://lewky.cn/posts/front-end-funny.html/><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css><link rel=stylesheet href=/css/style.min.css><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css><script type=application/ld+json>{"@context":"http://schema.org","@type":"BlogPosting","headline":"PostgreSQL - 字符串函数汇总","inLanguage":"","mainEntityOfPage":{"@type":"WebPage","@id":"https:\/\/lewky.cn\/posts\/postgresql-string.html\/"},"genre":"posts","keywords":"PostgreSQL, 工作记录","wordcount":2454,"url":"https:\/\/lewky.cn\/posts\/postgresql-string.html\/","datePublished":"2021-02-18T22:44:03+08:00","dateModified":"2022-03-02T22:44:03+08:00","license":"This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher":{"@type":"Organization","name":"雨临Lewis","logo":"https:\/\/lewky.cn\/images\/avatar.jpg"},"author":{"@type":"Person","name":"雨临Lewis"},"description":""}</script></head><body header-desktop=fixed header-mobile=auto><script type=text/javascript>(window.localStorage&&localStorage.getItem('theme')?localStorage.getItem('theme')==='dark':('auto'==='auto'?window.matchMedia('(prefers-color-scheme: dark)').matches:'auto'==='dark'))&&document.body.setAttribute('theme','dark');</script><div id=mask></div><div class=wrapper><header class=desktop id=header-desktop><div class=header-wrapper><a href=https://github.com/lewky class=github-corner target=_blank title="Follow me on GitHub" aria-label="Follow me on GitHub"><svg width="3.5rem" height="3.5rem" viewBox="0 0 250 250" style="fill:#70b7fd;color:#fff;position:absolute;top:0;border:0;left:0;transform:scale(-1,1)" aria-hidden="true"><path d="M0 0 115 115h15l12 27L250 250V0z"/><path d="M128.3 109C113.8 99.7 119 89.6 119 89.6 122 82.7 120.5 78.6 120.5 78.6 119.2 72 123.4 76.3 123.4 76.3 127.3 80.9 125.5 87.3 125.5 87.3 122.9 97.6 130.6 101.9 134.4 103.2" fill="currentcolor" style="transform-origin:130px 106px" class="octo-arm"/><path d="M115 115C114.9 115.1 118.7 116.5 119.8 115.4L133.7 101.6C136.9 99.2 139.9 98.4 142.2 98.6 133.8 88 127.5 74.4 143.8 58 148.5 53.4 154 51.2 159.7 51 160.3 49.4 163.2 43.6 171.4 40.1 171.4 40.1 176.1 42.5 178.8 56.2 183.1 58.6 187.2 61.8 190.9 65.4 194.5 69 197.7 73.2 200.1 77.6 213.8 80.2 216.3 84.9 216.3 84.9 212.7 93.1 206.9 96 205.4 96.6 205.1 102.4 203 107.8 198.3 112.5 181.9 128.9 168.3 122.5 157.7 114.1 157.9 116.9 156.7 120.9 152.7 124.9L141 136.5C139.8 137.7 141.6 141.9 141.8 141.8z" fill="currentcolor" class="octo-body"/></svg></a><div class=header-title><a href=/ title="Yulin Lewis' Blog"><span class=header-title-pre><i class="fas fa-fw fa-atom"></i></span>雨临Lewis</a></div><div class=menu><div class=menu-inner><div class=dropdown><a href=/posts/ class="menu-item menu-more dropbtn" title=点击查看所有文章><i class="fas fa-fw fa-archive"></i>归档</a><div class="menu-more-content dropdown-content"><a href=/categories/><i class="fas fa-fw fa-th"></i>分类 </a><a href=/tags/><i class="fas fa-fw fa-tag"></i>标签 </a><a href=/hot/ title=文章热度Top15><i class="fas fa-fw fa-fire"></i>热度 </a><a href=/donation/ title=感谢打赏，老板大气~><i class="fas fa-fw fa-donate"></i>打赏 </a><a href=/about/><i class="fas fa-fw fa-at"></i>关于</a></div></div><a class=menu-item href=/friends/ title=欢迎申请友链><i class="fas fa-fw fa-link"></i>友链</a><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn" title=一些有意思的东东~><i class="fas fa-fw fa-fan fa-spin"></i>趣味</a><div class="menu-more-content dropdown-content"><a href=/funny/high/ title="前方高能♂ 小心外放！" target=_blank rel=noopener><i class="fas fa-fw fa-dizzy"></i>燥起来！ </a><a href=/funny/mikutap/ title=初音未来音乐游戏 target=_blank rel=noopener><i class="fas fa-fw fa-music"></i>在线打碟 </a><a href=/funny/catch-the-cat/ title=逮住那只猫!><i class="fas fa-fw fa-cat"></i>抓住猫咪</a></div></div><div class=dropdown><a href=/ class="menu-item menu-more dropbtn" title=回到首页><i class="fas fa-fw fa-atom"></i>站点</a><div class="menu-more-content dropdown-content"><a href=/bbs/ title=来留言吧~><i class="fas fa-fw fa-comment"></i>公告留言 </a><a href=https://seo.chinaz.com target=_blank rel=noopener><i class="fas fa-fw fa-chart-line"></i>SEO查询 </a><a href=https://www.ping.cn/http/lewky.cn target=_blank rel=noopener><i class="fas fa-fw fa-bezier-curve"></i>网站测速 </a><a href=/posts/e62c38c4.html/><i class="fas fa-fw fa-cog fa-spin"></i>建站日志</a></div></div><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn"><i class="fas fa-fw fa-book"></i>文档</a><div class="menu-more-content dropdown-content"><a href=https://gohugo.io/documentation/ target=_blank rel=noopener><i class="fas fa-fw fa-star"></i>Hugo文档 </a><a href=https://javanote.doc.lewky.cn/ title=尚在完善中~ target=_blank rel=noopener><i class="fab fa-fw fa-java"></i>Java 笔记</a></div></div><a class=menu-item href=/search/ title=渲染搜索结果需要等待几秒钟~><i class="fas fa-fw fa-search"></i>搜索
</a><span class="menu-item delimiter"></span><a href=https://travellings.link target=_blank class=menu-item rel=noopener title=开往-友链接力><i class="fas fa-fw fa-subway"></i></a><a href=https://rssblog.vercel.app/ class=menu-item target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i></a><a href=https://github.com/lewky class=menu-item target=_blank rel=noopener title=GitHub><i class="fab fa-fw fa-github"></i></a></div></div></div></header><header class=mobile id=header-mobile><div class=header-container><div class=header-wrapper><div class=header-title><a href=/ title="Yulin Lewis' Blog"><span class=header-title-pre><i class="fas fa-fw fa-atom"></i></span>雨临Lewis</a></div><div class=menu-toggle id=menu-toggle-mobile><span></span><span></span><span></span></div></div><div class=menu id=menu-mobile><div class=dropdown><a href=/posts/ class="menu-item menu-more dropbtn" title=点击查看所有文章><i class="fas fa-fw fa-archive"></i>归档</a><div class="menu-more-content dropdown-content"><a href=/categories/><i class="fas fa-fw fa-th"></i>分类 </a><a href=/tags/><i class="fas fa-fw fa-tag"></i>标签 </a><a href=/hot/ title=文章热度Top15><i class="fas fa-fw fa-fire"></i>热度 </a><a href=/donation/ title=感谢打赏，老板大气~><i class="fas fa-fw fa-donate"></i>打赏 </a><a href=/about/><i class="fas fa-fw fa-at"></i>关于</a></div></div><a class=menu-item href=/friends/ title=欢迎申请友链><i class="fas fa-fw fa-link"></i>友链</a><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn" title=一些有意思的东东~><i class="fas fa-fw fa-fan fa-spin"></i>趣味</a><div class="menu-more-content dropdown-content"><a href=/funny/high/ title="前方高能♂ 小心外放！" target=_blank rel=noopener><i class="fas fa-fw fa-dizzy"></i>燥起来！ </a><a href=/funny/mikutap/ title=初音未来音乐游戏 target=_blank rel=noopener><i class="fas fa-fw fa-music"></i>在线打碟 </a><a href=/funny/catch-the-cat/ title=逮住那只猫!><i class="fas fa-fw fa-cat"></i>抓住猫咪</a></div></div><div class=dropdown><a href=/ class="menu-item menu-more dropbtn" title=回到首页><i class="fas fa-fw fa-atom"></i>站点</a><div class="menu-more-content dropdown-content"><a href=/bbs/ title=来留言吧~><i class="fas fa-fw fa-comment"></i>公告留言 </a><a href=https://seo.chinaz.com target=_blank rel=noopener><i class="fas fa-fw fa-chart-line"></i>SEO查询 </a><a href=https://www.ping.cn/http/lewky.cn target=_blank rel=noopener><i class="fas fa-fw fa-bezier-curve"></i>网站测速 </a><a href=/posts/e62c38c4.html/><i class="fas fa-fw fa-cog fa-spin"></i>建站日志</a></div></div><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn"><i class="fas fa-fw fa-book"></i>文档</a><div class="menu-more-content dropdown-content"><a href=https://gohugo.io/documentation/ target=_blank rel=noopener><i class="fas fa-fw fa-star"></i>Hugo文档 </a><a href=https://javanote.doc.lewky.cn/ title=尚在完善中~ target=_blank rel=noopener><i class="fab fa-fw fa-java"></i>Java 笔记</a></div></div><a class=menu-item href=/search/ title=渲染搜索结果需要等待几秒钟~><i class="fas fa-fw fa-search"></i>搜索</a>
<a href=https://travellings.link target=_blank class=menu-item rel=noopener title=开往-友链接力><i class="fas fa-fw fa-subway"></i></a><a href=https://rssblog.vercel.app/ class=menu-item target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i></a><a href=https://github.com/lewky class=menu-item target=_blank rel=noopener title=GitHub><i class="fab fa-fw fa-github"></i></a></div></div></header><div class="search-dropdown desktop"><div id=search-dropdown-desktop></div></div><div class="search-dropdown mobile"><div id=search-dropdown-mobile></div></div><main class=main><div class=container><div class=toc id=toc-auto><h2 class=toc-title>目录</h2><div class=toc-content id=toc-content-auto></div></div><article class="page single"><h1 class="single-title animated flipInX">PostgreSQL - 字符串函数汇总</h1><div class=post-meta><div class=post-meta-line><span class=post-author><a href=/ title=Author rel=author class=author><i class="fas fa-user-circle fa-fw"></i>雨临Lewis</a></span>&nbsp;<span class=post-category>收录于 <a href=/categories/postgresql/><i class="far fa-folder fa-fw"></i>PostgreSQL</a></span></div><div class=post-meta-line><i class="far fa-calendar fa-fw"></i>&nbsp;<time datetime=2021-02-18>2021-02-18</time>&nbsp;<i class="far fa-calendar-plus fa-fw"></i>&nbsp;<time datetime=2022-03-02>2022-03-02</time>&nbsp;<i class="fas fa-pencil-alt fa-fw"></i>&nbsp;约 2454 字
<i class="far fa-clock fa-fw"></i>&nbsp;预计阅读 5 分钟&nbsp;<span id=/posts/postgresql-string.html/ class=leancloud_visitors data-flag-title="PostgreSQL - 字符串函数汇总">
<i class="far fa-eye fa-fw"></i>&nbsp;<span class=leancloud-visitors-count></span>&nbsp;次阅读
</span>&nbsp;
<a href=#comments id=post-meta-vcount title=查看评论><i class="fas fa-comment fa-fw"></i>&nbsp;<span id=/posts/postgresql-string.html/ class=waline-comment-count></span>&nbsp;条评论</a></div></div><div class="details toc" id=toc-static kept><div class="details-summary toc-title"><span>目录</span>
<span><i class="details-icon fas fa-angle-right"></i></span></div><div class="details-content toc-content" id=toc-content-static><nav id=TableOfContents><ol><li><a href=#前言>前言</a></li><li><a href=#拼接字符串>拼接字符串</a></li><li><a href=#填充字符串>填充字符串</a></li><li><a href=#大小写转换>大小写转换</a></li><li><a href=#获取字符串长度>获取字符串长度</a></li><li><a href=#截取字符串>截取字符串</a></li><li><a href=#裁剪字符串>裁剪字符串</a></li><li><a href=#获取第一个字符的ascii码>获取第一个字符的ASCII码</a></li><li><a href=#计算string的md5散列>计算string的MD5散列</a></li><li><a href=#null和的区别与判断以及coalesce函数>null和'&lsquo;的区别与判断以及COALESCE函数</a></li><li><a href=#nullif函数>nullif函数</a></li><li><a href=#判断是否包含字符串>判断是否包含字符串</a></li><li><a href=#合并字符串>合并字符串</a></li><li><a href=#将字符串合并成一个数组>将字符串合并成一个数组</a></li><li><a href=#分割字符串>分割字符串</a></li><li><a href=#使用正则表达式分割字符串>使用正则表达式分割字符串</a></li><li><a href=#参考链接>参考链接</a></li></ol></nav></div></div><div class=content id=content><h2 id=前言>前言</h2><p>本文基于<code>PostgreSQL 12.6</code>版本，不同版本的函数可能存在差异。</p><h2 id=拼接字符串>拼接字符串</h2><p><code>||</code>是字符串连接操作符，在拼接字符串时要求前两个操作数至少有一个是字符串类型，不然会报错。如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=s1>&#39;a&#39;</span> <span class=o>||</span> <span class=mi>1</span><span class=p>;</span>
<span class=k>select</span> <span class=mi>2</span> <span class=o>||</span> <span class=s1>&#39;a&#39;</span> <span class=o>||</span> <span class=mi>1</span><span class=p>;</span>
<span class=k>select</span> <span class=mi>2</span> <span class=o>||</span> <span class=mi>44</span> <span class=o>||</span> <span class=s1>&#39;a&#39;</span> <span class=o>||</span> <span class=mi>1</span><span class=p>;</span> <span class=c1>--Error
</span></code></pre></td></tr></table></div></div><h2 id=填充字符串>填充字符串</h2><p><code>lpad(string text, length int [, fill text])</code>是在字符串左边填充字符，如果不指定要填充的字符，则默认填充空格，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>LPAD</span><span class=p>((</span><span class=mi>99</span> <span class=o>-</span> <span class=mi>1</span><span class=p>)::</span><span class=nb>text</span><span class=p>,</span> <span class=mi>6</span><span class=p>);</span> <span class=c1>-- 98
</span><span class=c1></span><span class=k>select</span> <span class=n>LPAD</span><span class=p>((</span><span class=mi>99</span> <span class=o>-</span> <span class=mi>1</span><span class=p>)::</span><span class=nb>text</span><span class=p>,</span> <span class=mi>6</span><span class=p>,</span> <span class=s1>&#39;0&#39;</span><span class=p>);</span> <span class=c1>--000098
</span><span class=c1></span><span class=k>select</span> <span class=n>LPAD</span><span class=p>((</span><span class=mi>99</span> <span class=o>+</span> <span class=mi>1</span><span class=p>)::</span><span class=nb>text</span><span class=p>,</span> <span class=mi>6</span><span class=p>,</span> <span class=s1>&#39;ab&#39;</span><span class=p>);</span> <span class=c1>--aba100
</span></code></pre></td></tr></table></div></div><p><code>rpad</code>函数与<code>lpad</code>函数相反，是在字符串右边填充字符。</p><h2 id=大小写转换>大小写转换</h2><p><code>upper</code>和<code>lower</code>函数，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>upper</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>);</span> <span class=c1>--TEST
</span><span class=c1></span><span class=k>select</span> <span class=k>lower</span><span class=p>(</span><span class=s1>&#39;TEST&#39;</span><span class=p>);</span> <span class=c1>--test
</span></code></pre></td></tr></table></div></div><h2 id=获取字符串长度>获取字符串长度</h2><p><code>length</code>、<code>char_length</code>和<code>character_length</code>函数，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>length</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>);</span> <span class=c1>--4
</span><span class=c1></span><span class=k>select</span> <span class=k>char_length</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>);</span> <span class=c1>--4
</span><span class=c1></span><span class=k>select</span> <span class=k>character_length</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>);</span> <span class=c1>--4
</span></code></pre></td></tr></table></div></div><h2 id=截取字符串>截取字符串</h2><p><code>substring</code>函数，支持下标范围截取或者正则表达式截取，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>substring</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span> <span class=k>from</span> <span class=mi>2</span> <span class=k>for</span> <span class=mi>4</span><span class=p>);</span> <span class=c1>--ostg
</span><span class=c1></span><span class=k>select</span> <span class=k>substring</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span> <span class=k>from</span> <span class=s1>&#39;[a-z]+&#39;</span><span class=p>);</span> <span class=c1>--ostgre
</span></code></pre></td></tr></table></div></div><p>也可以用<code>substr</code>函数，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>substr</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span><span class=p>,</span> <span class=mi>2</span><span class=p>,</span> <span class=mi>0</span><span class=p>);</span> <span class=c1>--空字符串
</span><span class=c1></span><span class=k>select</span> <span class=n>substr</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span><span class=p>,</span> <span class=mi>2</span><span class=p>,</span> <span class=mi>1</span><span class=p>);</span> <span class=c1>--o
</span><span class=c1></span><span class=k>select</span> <span class=n>substr</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span><span class=p>,</span> <span class=mi>2</span><span class=p>,</span> <span class=mi>4</span><span class=p>);</span> <span class=c1>--ostg
</span><span class=c1></span><span class=k>select</span> <span class=n>substr</span><span class=p>(</span><span class=s1>&#39;PostgreSQL&#39;</span><span class=p>,</span> <span class=mi>2</span><span class=p>);</span> <span class=c1>--ostgreSQL
</span></code></pre></td></tr></table></div></div><h2 id=裁剪字符串>裁剪字符串</h2><p><code>trim</code>函数，从字符串的开头/结尾/两边（leading/trailing/both）尽可能多地裁剪指定的字符，不指定则裁剪空白符，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span><span class=lnt>5
</span><span class=lnt>6
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>trim</span><span class=p>(</span><span class=k>leading</span> <span class=s1>&#39;x&#39;</span> <span class=k>from</span> <span class=s1>&#39;xTestxx&#39;</span><span class=p>);</span> <span class=c1>--Testxx
</span><span class=c1></span><span class=k>select</span> <span class=k>trim</span><span class=p>(</span><span class=k>trailing</span> <span class=s1>&#39;x&#39;</span> <span class=k>from</span> <span class=s1>&#39;xTestxx&#39;</span><span class=p>);</span> <span class=c1>--xTest
</span><span class=c1></span><span class=k>select</span> <span class=k>trim</span><span class=p>(</span><span class=k>both</span> <span class=s1>&#39;x&#39;</span> <span class=k>from</span> <span class=s1>&#39;xTestxx&#39;</span><span class=p>);</span> <span class=c1>--Test
</span><span class=c1></span>
<span class=k>select</span> <span class=k>trim</span><span class=p>(</span><span class=k>both</span> <span class=k>from</span> <span class=s1>&#39; Test &#39;</span><span class=p>);</span> <span class=c1>--Test
</span><span class=c1></span><span class=k>select</span> <span class=k>trim</span><span class=p>(</span><span class=s1>&#39; Test &#39;</span><span class=p>);</span> <span class=c1>--Test
</span></code></pre></td></tr></table></div></div><p>也可以用<code>ltrim</code>，<code>rtrim</code>或者<code>btrim</code>函数，效果同上：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>ltrim</span><span class=p>(</span><span class=s1>&#39;xTestxxy&#39;</span><span class=p>,</span> <span class=s1>&#39;xy&#39;</span><span class=p>);</span> <span class=c1>--Testxxy
</span><span class=c1></span><span class=k>select</span> <span class=n>rtrim</span><span class=p>(</span><span class=s1>&#39;xTestxxy&#39;</span><span class=p>,</span> <span class=s1>&#39;xy&#39;</span><span class=p>);</span> <span class=c1>--xTest
</span><span class=c1></span><span class=k>select</span> <span class=n>btrim</span><span class=p>(</span><span class=s1>&#39;xTestxxy&#39;</span><span class=p>,</span> <span class=s1>&#39;xy&#39;</span><span class=p>);</span> <span class=c1>--Test
</span></code></pre></td></tr></table></div></div><h2 id=获取第一个字符的ascii码>获取第一个字符的ASCII码</h2><p><code>ascii</code>函数，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>ascii</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>);</span> <span class=c1>--116
</span><span class=c1></span><span class=k>select</span> <span class=n>ascii</span><span class=p>(</span><span class=s1>&#39;t&#39;</span><span class=p>);</span> <span class=c1>--116
</span></code></pre></td></tr></table></div></div><p>如果想从ASCII码转成字符，则使用<code>chr</code>函数，参数是int，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>chr</span><span class=p>(</span><span class=mi>65</span><span class=p>);</span> <span class=c1>--A
</span></code></pre></td></tr></table></div></div><h2 id=计算string的md5散列>计算string的MD5散列</h2><p><code>md5</code>函数，以十六进制返回结果，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>md5</span><span class=p>(</span><span class=s1>&#39;abc&#39;</span><span class=p>);</span> <span class=c1>--900150983cd24fb0d6963f7d28e17f72
</span></code></pre></td></tr></table></div></div><h2 id=null和的区别与判断以及coalesce函数>null和'&lsquo;的区别与判断以及COALESCE函数</h2><p>null是一种类型，<code>''</code>是空字符串，打个比方，<code>''</code>是你参加了考试且得了零分，而null则是你压根就没有参加考试。</p><p>如果要在sql中对两者进行判断，是有区别的：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt> 1
</span><span class=lnt> 2
</span><span class=lnt> 3
</span><span class=lnt> 4
</span><span class=lnt> 5
</span><span class=lnt> 6
</span><span class=lnt> 7
</span><span class=lnt> 8
</span><span class=lnt> 9
</span><span class=lnt>10
</span><span class=lnt>11
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=c1>--null只能和is或is not搭配，不能使用=、!=或者&lt;&gt;
</span><span class=c1></span><span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>student</span> <span class=k>where</span> <span class=n>name</span> <span class=k>is</span> <span class=k>null</span><span class=p>;</span>
<span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>student</span> <span class=k>where</span> <span class=n>name</span> <span class=k>is</span> <span class=k>not</span> <span class=k>null</span><span class=p>;</span>

<span class=c1>--&#39;&#39;的判断可以使用=、!=或者&lt;&gt;
</span><span class=c1></span><span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>student</span> <span class=k>where</span> <span class=n>name</span> <span class=o>=</span> <span class=s1>&#39;&#39;</span><span class=p>;</span>
<span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>student</span> <span class=k>where</span> <span class=n>name</span> <span class=o>!=</span> <span class=s1>&#39;&#39;</span><span class=p>;</span>
<span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>student</span> <span class=k>where</span> <span class=n>name</span> <span class=o>&lt;&gt;</span> <span class=s1>&#39;&#39;</span><span class=p>;</span>

<span class=c1>--任何与null的运算比较，结果都是null
</span><span class=c1></span><span class=k>select</span> <span class=mi>1</span> <span class=o>&gt;</span> <span class=k>null</span><span class=p>;</span>  <span class=c1>--null
</span></code></pre></td></tr></table></div></div><p>COALESCE函数是返回参数中的第一个非null的值，在<code>PostgreSQL 10</code>里，它要求参数中至少有一个是非null的，如果参数都是null会报错。</p><p>不过在<code>PostgreSQL 12.6</code>版本COALESCE函数允许参数里只有null，此时返回值是null。</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span><span class=lnt>5
</span><span class=lnt>6
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>COALESCE</span><span class=p>(</span><span class=k>null</span><span class=p>,</span><span class=k>null</span><span class=p>);</span> <span class=o>//</span><span class=err>报错</span>
<span class=k>select</span> <span class=n>COALESCE</span><span class=p>(</span><span class=k>null</span><span class=p>,</span><span class=k>null</span><span class=p>,</span><span class=n>now</span><span class=p>()::</span><span class=nb>varchar</span><span class=p>,</span><span class=s1>&#39;&#39;</span><span class=p>);</span> <span class=o>//</span><span class=err>结果会得到当前的时间</span>
<span class=k>select</span> <span class=n>COALESCE</span><span class=p>(</span><span class=k>null</span><span class=p>,</span><span class=k>null</span><span class=p>,</span><span class=s1>&#39;&#39;</span><span class=p>,</span><span class=n>now</span><span class=p>()::</span><span class=nb>varchar</span><span class=p>);</span> <span class=o>//</span><span class=err>结果会得到</span><span class=s1>&#39;&#39;</span>

<span class=o>//</span><span class=err>可以和其他函数配合来实现一些复杂点的功能：查询学生姓名，如果学生名字为</span><span class=n>null或</span><span class=s1>&#39;&#39;</span><span class=err>则显示“姓名为空”</span>
<span class=k>select</span> <span class=k>case</span> <span class=k>when</span> <span class=n>coalesce</span><span class=p>(</span><span class=n>name</span><span class=p>,</span><span class=s1>&#39;&#39;</span><span class=p>)</span> <span class=o>=</span> <span class=s1>&#39;&#39;</span> <span class=k>then</span> <span class=s1>&#39;姓名为空&#39;</span> <span class=k>else</span> <span class=n>name</span> <span class=k>end</span> <span class=k>from</span> <span class=n>student</span><span class=p>;</span>
</code></pre></td></tr></table></div></div><h2 id=nullif函数>nullif函数</h2><p><code>nullif(a, b)</code>用来检测a参数是否与b参数相等，这里的a、b参数必须是同一种数据类型，否则会报错。当a参数与b参数相等时会返回null，否则返回a参数。</p><p>可以用这个函数来检测期望以外的值，一般用于检测字符串比较多。如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>nullif</span><span class=p>(</span><span class=s1>&#39;test&#39;</span><span class=p>,</span> <span class=s1>&#39;unexpected&#39;</span><span class=p>);</span>		<span class=c1>--test
</span><span class=c1></span><span class=k>select</span> <span class=k>nullif</span><span class=p>(</span><span class=s1>&#39;unexpected&#39;</span><span class=p>,</span> <span class=s1>&#39;unexpected&#39;</span><span class=p>);</span>	<span class=c1>--null
</span><span class=c1></span>
<span class=k>select</span> <span class=k>nullif</span><span class=p>(</span><span class=mi>233</span><span class=p>,</span> <span class=mi>111</span><span class=p>);</span>		<span class=c1>--233
</span></code></pre></td></tr></table></div></div><h2 id=判断是否包含字符串>判断是否包含字符串</h2><p><code>position</code>函数会返回字符串首次出现的位置，如果没有出现则返回0。因此可以通过返回值是否大于0来判断是否包含指定的字符串。</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=k>position</span><span class=p>(</span><span class=s1>&#39;aa&#39;</span> <span class=k>in</span> <span class=s1>&#39;abcd&#39;</span><span class=p>);</span>	<span class=c1>--0
</span><span class=c1></span><span class=k>select</span> <span class=k>position</span><span class=p>(</span><span class=s1>&#39;bc&#39;</span> <span class=k>in</span> <span class=s1>&#39;abcd&#39;</span><span class=p>);</span>	<span class=c1>--2
</span><span class=c1></span><span class=k>select</span> <span class=k>position</span><span class=p>(</span><span class=s1>&#39;bc&#39;</span> <span class=k>in</span> <span class=s1>&#39;abcdabc&#39;</span><span class=p>);</span>	<span class=c1>--2
</span></code></pre></td></tr></table></div></div><p><code>strpos</code>函数也是同样的效果：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>strpos</span><span class=p>(</span><span class=s1>&#39;abcd&#39;</span><span class=p>,</span><span class=s1>&#39;aa&#39;</span><span class=p>);</span>		<span class=c1>--0
</span><span class=c1></span><span class=k>select</span> <span class=n>strpos</span><span class=p>(</span><span class=s1>&#39;abcd&#39;</span><span class=p>,</span><span class=s1>&#39;bc&#39;</span><span class=p>);</span>		<span class=c1>--2
</span><span class=c1></span><span class=k>select</span> <span class=n>strpos</span><span class=p>(</span><span class=s1>&#39;abcdabc&#39;</span><span class=p>,</span><span class=s1>&#39;bc&#39;</span><span class=p>);</span>	<span class=c1>--2
</span></code></pre></td></tr></table></div></div><p>此外还可以用正则表达式来判断，返回值是true或false：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=s1>&#39;abcd&#39;</span> <span class=o>~</span> <span class=s1>&#39;aa&#39;</span><span class=p>;</span>		<span class=c1>--false
</span><span class=c1></span><span class=k>select</span> <span class=s1>&#39;abcd&#39;</span> <span class=o>~</span> <span class=s1>&#39;bc&#39;</span><span class=p>;</span>		<span class=c1>--true
</span><span class=c1></span><span class=k>select</span> <span class=s1>&#39;abcdabc&#39;</span> <span class=o>~</span> <span class=s1>&#39;bc&#39;</span><span class=p>;</span>	<span class=c1>--true
</span></code></pre></td></tr></table></div></div><h2 id=合并字符串>合并字符串</h2><p><code>string_agg</code>函数可以将一个字符串列合并成一个字符串，该函数需要指定分隔符，还可以指定合并时的顺序，或者是对合并列进行去重：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt> 1
</span><span class=lnt> 2
</span><span class=lnt> 3
</span><span class=lnt> 4
</span><span class=lnt> 5
</span><span class=lnt> 6
</span><span class=lnt> 7
</span><span class=lnt> 8
</span><span class=lnt> 9
</span><span class=lnt>10
</span><span class=lnt>11
</span><span class=lnt>12
</span><span class=lnt>13
</span><span class=lnt>14
</span><span class=lnt>15
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>ref_no</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--结果如下：
</span><span class=c1>--ITM2105-000001
</span><span class=c1>--ITM2105-000002
</span><span class=c1>--ITM2105-000003
</span><span class=c1>--ITM2105-000003
</span><span class=c1></span>
<span class=k>select</span> <span class=n>string_agg</span><span class=p>(</span><span class=n>ref_no</span><span class=p>,</span> <span class=s1>&#39;,&#39;</span><span class=p>)</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--合并结果：ITM2105-000001,ITM2105-000002,ITM2105-000003,ITM2105-000003
</span><span class=c1></span>
<span class=k>select</span> <span class=n>string_agg</span><span class=p>(</span><span class=k>distinct</span> <span class=n>ref_no</span><span class=p>,</span> <span class=s1>&#39;,&#39;</span><span class=p>)</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--合并结果：ITM2105-000001,ITM2105-000002,ITM2105-000003
</span><span class=c1></span>
<span class=k>select</span> <span class=n>string_agg</span><span class=p>(</span><span class=k>distinct</span> <span class=n>ref_no</span><span class=p>,</span> <span class=s1>&#39;,&#39;</span> <span class=k>order</span> <span class=k>by</span> <span class=n>ref_no</span> <span class=k>desc</span><span class=p>)</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--合并结果：ITM2105-000003,ITM2105-000002,ITM2105-000001
</span></code></pre></td></tr></table></div></div><h2 id=将字符串合并成一个数组>将字符串合并成一个数组</h2><p><code>array_agg</code>和<code>string_agg</code>函数类似，但会把一个字符串列合并成一个数组对象，同样支持指定合并顺序和去重操作；合并成数组后意味着你可以像数组那样去读取它，<strong>需要注意的是，数据库的数组下标是从1开始的</strong>，而不是从0开始：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span><span class=lnt>5
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>array_agg</span><span class=p>(</span><span class=k>distinct</span> <span class=n>ref_no</span><span class=p>)</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--合并结果：{ITM2105-000001,ITM2105-000002,ITM2105-000003}
</span><span class=c1></span>
<span class=k>select</span> <span class=p>(</span><span class=n>array_agg</span><span class=p>(</span><span class=k>distinct</span> <span class=n>ref_no</span> <span class=k>order</span> <span class=k>by</span> <span class=n>ref_no</span> <span class=k>desc</span><span class=p>))[</span><span class=mi>1</span><span class=p>]</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--结果：ITM2105-000003
</span></code></pre></td></tr></table></div></div><p>该函数还可以搭配<code>array_to_string</code>函数将数组转合并成一个字符串：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>array_to_string</span><span class=p>(</span><span class=n>array_agg</span><span class=p>(</span><span class=k>distinct</span> <span class=n>ref_no</span><span class=p>),</span> <span class=s1>&#39;&amp;&#39;</span><span class=p>)</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=k>between</span> <span class=s1>&#39;2021-05-05&#39;</span> <span class=k>and</span> <span class=s1>&#39;2021-05-30 16:13:25&#39;</span><span class=p>;</span>
<span class=c1>--合并结果：ITM2105-000001&amp;ITM2105-000002&amp;ITM2105-000003
</span></code></pre></td></tr></table></div></div><h2 id=分割字符串>分割字符串</h2><p><code>string_to_array</code>函数可以分割字符串，返回值是一个数组：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>string_to_array</span><span class=p>(</span><span class=s1>&#39;ITM2105-000001&amp;ITM2105-000002&amp;ITM2105-000003&#39;</span><span class=p>,</span> <span class=s1>&#39;&amp;&#39;</span><span class=p>);</span>
<span class=c1>--结果：{ITM2105-000001,ITM2105-000002,ITM2105-000003}
</span></code></pre></td></tr></table></div></div><h2 id=使用正则表达式分割字符串>使用正则表达式分割字符串</h2><p><code>regexp_split_to_table</code>可以通过正则表达式来定义分隔符，将一个字符串分割成多个字符串，即将一条数据分隔成多条数据。有两个参数，第一个参数是需要被分隔的字符串，第二个参数是正则表达式。</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span><span class=lnt>4
</span><span class=lnt>5
</span><span class=lnt>6
</span><span class=lnt>7
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>regexp_split_to_table</span><span class=p>(</span><span class=k>trim</span><span class=p>(</span><span class=k>both</span> <span class=s1>&#39;{}&#39;</span> <span class=k>from</span> <span class=s1>&#39;{lbl.codelist} &gt; {lbl.codelist.tabHeader} &gt; {lbl.codelist.tabHeader.codelists}: 115 (993) &gt; {lbl.codelist.tabHeader.codelists.name}&#39;</span><span class=p>),</span> <span class=s1>&#39;}[^}]*{&#39;</span><span class=p>)</span> <span class=k>as</span> <span class=n>label</span><span class=p>;</span>

<span class=c1>--结果：原字符串被分隔成四个字符串，并返回四行数据
</span><span class=c1></span><span class=n>lbl</span><span class=p>.</span><span class=n>codelist</span>
<span class=n>lbl</span><span class=p>.</span><span class=n>codelist</span><span class=p>.</span><span class=n>tabHeader</span>
<span class=n>lbl</span><span class=p>.</span><span class=n>codelist</span><span class=p>.</span><span class=n>tabHeader</span><span class=p>.</span><span class=n>codelists</span>
<span class=n>lbl</span><span class=p>.</span><span class=n>codelist</span><span class=p>.</span><span class=n>tabHeader</span><span class=p>.</span><span class=n>codelists</span><span class=p>.</span><span class=n>name</span>
</code></pre></td></tr></table></div></div><h2 id=参考链接>参考链接</h2><ul><li><a href=https://www.cnblogs.com/brucexl/p/7561292.html target=_blank rel="noopener noreffer">postgresql 常用函数汇总</a></li><li><a href=https://www.runoob.com/postgresql/postgresql-functions.html target=_blank rel="noopener noreffer">字符串函数和操作符</a></li><li><a href=https://blog.csdn.net/luojinbai/article/details/45461837 target=_blank rel="noopener noreffer">PostgreSQL 判断字符串包含的几种方法</a></li><li><a href=https://blog.csdn.net/u011944141/article/details/78902678/ target=_blank rel="noopener noreffer">PostgreSql 聚合函数string_agg与array_agg</a></li></ul><div class=post-reward><input type=checkbox name=reward id=reward hidden>
<label class=reward-button for=reward>赞赏支持</label><div class=qr-code><label class=qr-code-image for=reward><img class=image src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/common/wechat.png>
<span>微信打赏</span></label>
<label class=qr-code-image for=reward><img class=image src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/common/alipay.png>
<span>支付宝打赏</span></label></div></div></div><div class=post-footer id=post-footer><div class=post-info><div class=post-info-line><div class=post-info-mod><span>更新于 2022-03-02</span></div><div class=post-info-license><span><a rel="license external nofollow noopener noreffer" href=https://creativecommons.org/licenses/by-nc/4.0/ target=_blank>CC BY-NC 4.0</a></span></div></div><div class=post-info-line><div class=post-info-md><span><a class=link-to-markdown href=/posts/postgresql-string.html/index.md target=_blank>阅读原始文档</a></span></div><div class=post-info-share><span><a href=javascript:void(0); title="分享到 Twitter" data-sharer=twitter data-url=https://lewky.cn/posts/postgresql-string.html/ data-title="PostgreSQL - 字符串函数汇总" data-hashtags=PostgreSQL,工作记录><i class="fab fa-twitter fa-fw"></i></a><a href=javascript:void(0); title="分享到 Facebook" data-sharer=facebook data-url=https://lewky.cn/posts/postgresql-string.html/ data-hashtag=PostgreSQL><i class="fab fa-facebook-square fa-fw"></i></a><a href=javascript:void(0); title="分享到 Hacker News" data-sharer=hackernews data-url=https://lewky.cn/posts/postgresql-string.html/ data-title="PostgreSQL - 字符串函数汇总"><i class="fab fa-hacker-news fa-fw"></i></a><a href=javascript:void(0); title="分享到 Line" data-sharer=line data-url=https://lewky.cn/posts/postgresql-string.html/ data-title="PostgreSQL - 字符串函数汇总"><i data-svg-src=https://cdn.jsdelivr.net/npm/simple-icons@2.14.0/icons/line.svg></i></a><a href=javascript:void(0); title="分享到 微博" data-sharer=weibo data-url=https://lewky.cn/posts/postgresql-string.html/ data-title="PostgreSQL - 字符串函数汇总"><i class="fab fa-weibo fa-fw"></i></a></span></div></div></div><div class=post-info-more><section class=post-tags><i class="fas fa-tags fa-fw"></i>&nbsp;<a href=/tags/postgresql/>PostgreSQL</a>,&nbsp;<a href=/tags/%E5%B7%A5%E4%BD%9C%E8%AE%B0%E5%BD%95/>工作记录</a></section><section><span><a href=javascript:void(0); onclick=window.history.back();>返回</a></span>&nbsp;|&nbsp;<span><a href=/>主页</a></span></section></div><div class=post-nav><a href=/posts/remote-desktop-issue.html/ class=prev rel=prev title=远程桌面无法复制粘贴><i class="fas fa-angle-left fa-fw"></i>远程桌面无法复制粘贴</a>
<a href=/posts/front-end-funny.html/ class=next rel=next title=分享一些有趣的前端图形和页面>分享一些有趣的前端图形和页面<i class="fas fa-angle-right fa-fw"></i></a></div></div><div id=comments><div id=waline></div><script src=https://cdn.jsdelivr.net/npm/@waline/client/dist/Waline.min.js></script><script>new Waline({el:'#waline',meta:["nick","mail","link"],requiredMeta:["nick","mail"],login:"force",placeholder:"为防恶意灌水攻击，评论前需注册并登录，望见谅~",serverURL:"https://comment.lewky.cn/",avatarCDN:"https://sdn.geekzu.org/avatar/",pageSize:20,avatar:"retro",lang:"zh-CN",visitor:true,highlight:true,uploadImage:false,emoji:['https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/嘉然今天吃什么','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/大航海嘉然','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/向晚大魔王','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/贝拉kira','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/珈乐Carol','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/乃琳Queen','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/EveOneCat','https://cdn.jsdelivr.net/gh/walinejs/emojis@1.0.0/weibo','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/滑稽','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/default']});</script></div></article></div></main><footer class=footer><div class=footer-container><div class=footer-line><span id=run-time></span>|
<a href=http://rssblog.vercel.app/ target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i>&nbsp;RSSBlog</a></div><div class=footer-line><i class="far fa-copyright fa-fw"></i><span itemprop=copyrightYear>2018 - 2022</span><span class=author itemprop=copyrightHolder>&nbsp;<a href=/ target=_blank>雨临Lewis</a></span>&nbsp;|&nbsp;<span class=license><a rel="license external nofollow noopener noreffer" href=https://creativecommons.org/licenses/by-nc/4.0/ target=_blank>CC BY-NC 4.0</a></span><span class=icp-splitter>&nbsp;|&nbsp;</span><br class=icp-br><span class=icp><a target=_blank href=http://www.beian.miit.gov.cn/ style=font-weight:700>粤ICP备19103822</a></span></div></div></footer></div><div id=fixed-buttons><a href=# id=back-to-top class=fixed-button title=回到顶部><i class="fas fa-arrow-up fa-fw"></i></a><a href=# id=view-comments class=fixed-button title=查看评论><i class="fas fa-comment fa-fw"></i></a></div><div class=sidebar_wo><div id=leimu><img src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/b2t/leimuA.png alt=雷姆 onmouseover="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/leimuB.png'" onmouseout="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/leimuA.png'" title=回到顶部></div><div class=sidebar_wo id=lamu><img src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/b2t/lamuA.png alt=雷姆 onmouseover="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/lamuB.png'" onmouseout="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/lamuA.png'" title=回到底部></div></div><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css><script type=text/javascript src=https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js></script><script type=text/javascript>window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":30},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true}};</script><script type=text/javascript src=/js/theme.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/jquery@2.1.3/dist/jquery.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/jquery-backstretch@2.1.18/jquery.backstretch.min.js></script><script>var $cdnPrefix="https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master";</script><script type=text/javascript src=/js/custom.js></script><script>var _hmt=_hmt||[];(function(){var hm=document.createElement("script");hm.src="https://hm.baidu.com/hm.js?6f278ed0fdb01edb3b1e7398379e5432";var s=document.getElementsByTagName("script")[0];s.parentNode.insertBefore(hm,s);})();</script></body></html>